
const xlsx = require('node-xlsx');
const db = require('db');
const log = require('tools/log');

const options = {
    '!cols': [
        { wch: 7 },
        { wch: 7 },
        { wch: 10 },
        { wch: 10 },
        { wch: 15 },
        { wch: 10 },
        { wch: 30 },
        { wch: 10 },
        { wch: 10 },
        { wch: 10 },
        { wch: 15 },
        { wch: 15 },
        { wch: 15 },
        { wch: 15 },
        { wch: 15 },
    ]
};

module.exports = async (ctx, next) => {

    const { connection, run } = db.init();
    try {
        const list = await orderlist(run);
        ctx.status = 200;
        ctx.body = xlsx.build([{ name: "结算单", data: format(list), options }]);
    } catch (error) {
        ctx.status = error.status;
        ctx.body = error.err;
        log('---------------------------------------------------')
        console.log(error)
    }
}


/** 获取列表 */
async function orderlist(run) {
    const sqlStr = `
        SELECT o.*, c.clientName
        FROM orders AS o
        LEFT JOIN client AS c ON o.clientId = c.id 
    `
    const result = await run(sqlStr);
    if (result.err) {
        throw {
            status: 500,
            err: result.err,
        }
    }

    return result.rows;
}

/** 数据格式 */
function format(list) {
    const obj = list.map(e => ({
        '公司': companyStringMap[e.company] || e.company,
        '商务': e.commercialSpecialistName,
        'PM': e.pmName,
        '订单状态': e.orderStatus,
        '接单时间': e.orderTime,
        '订单编号': e.orderNo,
        '归属部门': e.deptName,
        '客户全称': e.clientName,
        '项目名称': e.project,
        '合作模式': e.cooperationMode,
        '承接类型': e.undertakingType,
        '计划开始时间': e.planStartTime,
        '实际开始时间': e.startTime,
        '计划完成时间': e.scheduledCompletionTime,
        '实际完成时间': e.completionTime,
        '单价（元）': e.price,
        '制作人天': e.personDays,
        '商务人天': e.businessDay,
        '总金额（元）': { f: '=P{i}*R{i}' },
        '付款周期（天）': e.paymentCycle,
        '订单明细': e.details,
    }))

    const keys = Object.keys(obj[0]);

    const values = obj
        .map(e => Object.values(e))
        .map((a, i) => {
            return a.map(e => {
                return replace(e, /\{i\}/g, i + 2);
            });
        })

    return [
        keys,
        ...values
    ]
}

const replace = (e, regExp, replaceValue) => {
    if (typeof e === 'string') {
        return e.replace(regExp, replaceValue);
    } else if (e && typeof e.f === 'string') {
        e.f = e.f.replace(regExp, replaceValue);
        return e;
    }
    return e;
}

const companyStringMap = {
    'hh': '火猴',
    'ydw': '悦逗蛙',
}